use anyhow::Result;
use serde::{Deserialize, Serialize};
use tracing::info;
use std::collections::HashMap;

/// SQL generator for converting natural language to SQL
#[derive(Debug)]
pub struct SqlGenerator {
    /// Common SQL patterns and templates
    patterns: HashMap<String, SqlTemplate>,
    /// Database schema context
    schema_context: Option<SchemaContext>,
}

/// SQL template for pattern matching
#[derive(Debug, Clone)]
pub struct SqlTemplate {
    pub template: String,
    pub description: String,
    pub confidence: f32,
}

/// Database schema context
#[derive(Debug, Clone)]
pub struct SchemaContext {
    pub tables: Vec<TableInfo>,
    pub relationships: Vec<Relationship>,
}

/// Table information
#[derive(Debug, Clone)]
pub struct TableInfo {
    pub name: String,
    pub columns: Vec<ColumnInfo>,
    pub description: Option<String>,
}

/// Column information
#[derive(Debug, Clone)]
pub struct ColumnInfo {
    pub name: String,
    pub data_type: String,
    pub nullable: bool,
    pub description: Option<String>,
}

/// Table relationship
#[derive(Debug, Clone)]
pub struct Relationship {
    pub from_table: String,
    pub from_column: String,
    pub to_table: String,
    pub to_column: String,
    pub relationship_type: RelationshipType,
}

/// Type of relationship between tables
#[derive(Debug, Clone)]
pub enum RelationshipType {
    OneToOne,
    OneToMany,
    ManyToMany,
}

/// Generated SQL result
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct GeneratedSql {
    pub sql: String,
    pub explanation: String,
    pub confidence: f32,
    pub warnings: Vec<String>,
    pub suggested_tables: Vec<String>,
    pub query_type: QueryType,
}

/// Type of SQL query
#[derive(Debug, Clone, Serialize, Deserialize)]
pub enum QueryType {
    Select,
    Insert,
    Update,
    Delete,
    Create,
    Drop,
    Alter,
    Unknown,
}

impl SqlGenerator {
    /// Create a new SQL generator
    pub fn new() -> Self {
        let mut patterns = HashMap::new();

        // Initialize common SQL patterns
        patterns.insert("find_all".to_string(), SqlTemplate {
            template: "SELECT * FROM {table}".to_string(),
            description: "Find all records from a table".to_string(),
            confidence: 0.9,
        });

        patterns.insert("find_by_id".to_string(), SqlTemplate {
            template: "SELECT * FROM {table} WHERE id = {value}".to_string(),
            description: "Find a record by ID".to_string(),
            confidence: 0.95,
        });

        patterns.insert("count_all".to_string(), SqlTemplate {
            template: "SELECT COUNT(*) FROM {table}".to_string(),
            description: "Count all records in a table".to_string(),
            confidence: 0.9,
        });

        patterns.insert("insert_record".to_string(), SqlTemplate {
            template: "INSERT INTO {table} ({columns}) VALUES ({values})".to_string(),
            description: "Insert a new record".to_string(),
            confidence: 0.8,
        });

        patterns.insert("update_record".to_string(), SqlTemplate {
            template: "UPDATE {table} SET {assignments} WHERE {condition}".to_string(),
            description: "Update existing records".to_string(),
            confidence: 0.8,
        });

        patterns.insert("delete_record".to_string(), SqlTemplate {
            template: "DELETE FROM {table} WHERE {condition}".to_string(),
            description: "Delete records".to_string(),
            confidence: 0.85,
        });

        Self {
            patterns,
            schema_context: None,
        }
    }

    /// Set schema context for better SQL generation
    pub fn set_schema_context(&mut self, context: SchemaContext) {
        self.schema_context = Some(context);
    }
    
    /// Generate SQL from natural language prompt
    pub async fn generate(&self, prompt: &str, schema_context: Option<&str>) -> Result<GeneratedSql> {
        info!("Generating SQL from prompt: {}", prompt);

        let prompt_lower = prompt.to_lowercase();
        let mut warnings = Vec::new();
        let mut suggested_tables = Vec::new();

        // Analyze the prompt to determine intent and extract entities
        let analysis = self.analyze_prompt(&prompt_lower);

        // Extract table names from prompt or schema context
        if let Some(schema) = schema_context {
            suggested_tables.extend(self.extract_table_names_from_schema(schema));
        }
        suggested_tables.extend(self.extract_table_names_from_prompt(&prompt_lower));

        // Generate SQL based on analysis
        let (sql, confidence, query_type) = self.generate_sql_from_analysis(&analysis, &suggested_tables)?;

        // Add warnings if confidence is low
        if confidence < 0.7 {
            warnings.push("Low confidence in SQL generation. Please review the generated query.".to_string());
        }

        if suggested_tables.is_empty() {
            warnings.push("No table names detected. Using default table 'users'.".to_string());
            suggested_tables.push("users".to_string());
        }

        let explanation = self.generate_explanation(&prompt, &sql, &query_type);

        Ok(GeneratedSql {
            sql,
            explanation,
            confidence,
            warnings,
            suggested_tables,
            query_type,
        })
    }

    /// Optimize an existing SQL query
    pub async fn optimize(&self, sql: &str) -> Result<String> {
        info!("Optimizing SQL query: {}", sql);

        let mut optimized = sql.to_string();
        let sql_upper = sql.to_uppercase();

        // Basic optimization suggestions
        let mut optimizations = Vec::new();

        // Check for SELECT *
        if sql_upper.contains("SELECT *") {
            optimizations.push("Consider specifying only needed columns instead of SELECT *");
            optimized = optimized.replace("SELECT *", "SELECT column1, column2, column3 -- Specify needed columns");
        }

        // Check for missing WHERE clause in UPDATE/DELETE
        if (sql_upper.contains("UPDATE") || sql_upper.contains("DELETE")) && !sql_upper.contains("WHERE") {
            optimizations.push("⚠️ WARNING: UPDATE/DELETE without WHERE clause affects all rows");
        }

        // Check for missing LIMIT in large queries
        if sql_upper.contains("SELECT") && !sql_upper.contains("LIMIT") && !sql_upper.contains("TOP") {
            optimizations.push("Consider adding LIMIT clause for large result sets");
            if !optimized.trim_end().ends_with(';') {
                optimized.push_str(" LIMIT 1000");
            } else {
                optimized = optimized.trim_end_matches(';').to_string() + " LIMIT 1000;";
            }
        }

        // Check for potential index usage
        if sql_upper.contains("WHERE") {
            optimizations.push("Ensure WHERE clause columns are indexed for better performance");
        }

        // Add optimization comments
        if !optimizations.is_empty() {
            let mut result = format!("-- Optimization suggestions:\n");
            for opt in optimizations {
                result.push_str(&format!("-- {}\n", opt));
            }
            result.push_str("\n");
            result.push_str(&optimized);
            Ok(result)
        } else {
            Ok(format!("-- Query appears to be well-optimized\n{}", optimized))
        }
    }
    
    /// Optimize an existing SQL query
    pub async fn optimize(&self, sql: &str) -> Result<String> {
        info!("Optimizing SQL query");
        
        // TODO: Implement actual SQL optimization
        // This is a placeholder implementation
        
        let optimized = if sql.contains("SELECT *") {
            sql.replace("SELECT *", "SELECT id, name, email")
        } else {
            sql.to_string()
        };
        
        Ok(optimized)
    }
    
    /// Validate SQL syntax
    pub fn validate(&self, sql: &str) -> Result<bool> {
        info!("Validating SQL syntax");
        
        // TODO: Implement actual SQL validation
        // This is a placeholder implementation
        
        let sql = sql.trim().to_lowercase();
        
        // Basic validation
        if sql.is_empty() {
            return Ok(false);
        }
        
        // Check for basic SQL keywords
        let valid_starts = ["select", "insert", "update", "delete", "create", "drop", "alter"];
        let is_valid = valid_starts.iter().any(|&keyword| sql.starts_with(keyword));
        
        Ok(is_valid)
    }
    
    /// Extract table names from SQL
    pub fn extract_tables(&self, sql: &str) -> Vec<String> {
        info!("Extracting table names from SQL");
        
        // TODO: Implement proper SQL parsing
        // This is a simple placeholder implementation
        
        let mut tables = Vec::new();
        let sql_lower = sql.to_lowercase();
        
        // Simple pattern matching for common cases
        if let Some(from_pos) = sql_lower.find("from ") {
            let after_from = &sql_lower[from_pos + 5..];
            if let Some(space_pos) = after_from.find(' ') {
                let table_name = after_from[..space_pos].trim();
                if !table_name.is_empty() {
                    tables.push(table_name.to_string());
                }
            }
        }
        
        tables
    }
}

    /// Analyze the natural language prompt
    fn analyze_prompt(&self, prompt: &str) -> PromptAnalysis {
        let mut analysis = PromptAnalysis {
            intent: Intent::Unknown,
            entities: HashMap::new(),
            conditions: Vec::new(),
            aggregations: Vec::new(),
            limit: None,
            order_by: None,
        };

        // Determine intent
        if prompt.contains("find") || prompt.contains("get") || prompt.contains("show") ||
           prompt.contains("list") || prompt.contains("display") || prompt.contains("select") {
            analysis.intent = Intent::Select;
        } else if prompt.contains("add") || prompt.contains("insert") || prompt.contains("create") {
            analysis.intent = Intent::Insert;
        } else if prompt.contains("update") || prompt.contains("modify") || prompt.contains("change") ||
                  prompt.contains("edit") {
            analysis.intent = Intent::Update;
        } else if prompt.contains("delete") || prompt.contains("remove") || prompt.contains("drop") {
            analysis.intent = Intent::Delete;
        } else if prompt.contains("count") || prompt.contains("how many") {
            analysis.intent = Intent::Count;
        }

        // Extract entities (potential table names and columns)
        let words: Vec<&str> = prompt.split_whitespace().collect();
        for (i, word) in words.iter().enumerate() {
            // Look for potential table names after "from", "in", "into"
            if (word == &"from" || word == &"in" || word == &"into") && i + 1 < words.len() {
                let potential_table = words[i + 1].trim_end_matches(|c| c == ',' || c == '.' || c == ';');
                analysis.entities.insert("table".to_string(), potential_table.to_string());
            }

            // Look for conditions after "where"
            if word == &"where" && i + 3 < words.len() {
                let condition = format!("{} {} {}", words[i + 1], words[i + 2], words[i + 3]);
                analysis.conditions.push(condition);
            }

            // Look for aggregations
            if word == &"average" || word == &"avg" || word == &"sum" || word == &"count" ||
               word == &"maximum" || word == &"max" || word == &"minimum" || word == &"min" {
                if i + 1 < words.len() {
                    let agg_func = match *word {
                        "average" | "avg" => "AVG",
                        "sum" => "SUM",
                        "count" => "COUNT",
                        "maximum" | "max" => "MAX",
                        "minimum" | "min" => "MIN",
                        _ => "COUNT",
                    };
                    let column = words[i + 1].trim_end_matches(|c| c == ',' || c == '.' || c == ';');
                    analysis.aggregations.push((agg_func.to_string(), column.to_string()));
                }
            }

            // Look for limit
            if (word == &"limit" || word == &"top" || (word == &"first" && i + 1 < words.len())) && i + 1 < words.len() {
                if let Ok(limit) = words[i + 1].parse::<i32>() {
                    analysis.limit = Some(limit);
                }
            }

            // Look for order by
            if (word == &"order" || word == &"sort") && i + 2 < words.len() && words[i + 1] == "by" {
                let column = words[i + 2].trim_end_matches(|c| c == ',' || c == '.' || c == ';');
                let direction = if i + 3 < words.len() &&
                                 (words[i + 3] == "descending" || words[i + 3] == "desc") {
                    "DESC"
                } else {
                    "ASC"
                };
                analysis.order_by = Some((column.to_string(), direction.to_string()));
            }
        }

        analysis
    }

    /// Extract table names from the prompt
    fn extract_table_names_from_prompt(&self, prompt: &str) -> Vec<String> {
        let mut tables = Vec::new();

        // Common singular/plural table names
        let common_tables = [
            "user", "users",
            "customer", "customers",
            "product", "products",
            "order", "orders",
            "category", "categories",
            "employee", "employees",
            "transaction", "transactions",
            "account", "accounts",
            "item", "items",
            "invoice", "invoices",
        ];

        for table in common_tables.iter() {
            if prompt.contains(table) {
                // Convert to singular form for consistency
                let normalized = if table.ends_with("s") && !table.ends_with("ss") {
                    table.trim_end_matches('s')
                } else {
                    table
                };

                // Add to tables if not already present
                if !tables.iter().any(|t| t == normalized) {
                    tables.push(normalized.to_string());
                }
            }
        }

        tables
    }

    /// Extract table names from schema context
    fn extract_table_names_from_schema(&self, schema: &str) -> Vec<String> {
        let mut tables = Vec::new();

        // Simple extraction based on CREATE TABLE statements
        for line in schema.lines() {
            let line_lower = line.to_lowercase();
            if line_lower.contains("create table") {
                let parts: Vec<&str> = line_lower.split_whitespace().collect();
                if parts.len() >= 3 {
                    let table_name = parts[2].trim_matches(|c| c == '`' || c == '"' || c == '\'' || c == '(' || c == ';');
                    tables.push(table_name.to_string());
                }
            }
        }

        tables
    }

    /// Generate SQL based on prompt analysis
    fn generate_sql_from_analysis(&self, analysis: &PromptAnalysis, suggested_tables: &[String]) -> Result<(String, f32, QueryType)> {
        let default_table = suggested_tables.first()
            .map(|s| s.as_str())
            .unwrap_or("users");

        let table_name = analysis.entities.get("table")
            .map(|s| s.as_str())
            .unwrap_or(default_table);

        match analysis.intent {
            Intent::Select => {
                let mut sql = String::from("SELECT ");

                // Handle aggregations
                if !analysis.aggregations.is_empty() {
                    let aggs: Vec<String> = analysis.aggregations.iter()
                        .map(|(func, col)| format!("{}({})", func, col))
                        .collect();
                    sql.push_str(&aggs.join(", "));
                } else {
                    sql.push_str("*");
                }

                sql.push_str(&format!(" FROM {}", table_name));

                // Add WHERE clause if conditions exist
                if !analysis.conditions.is_empty() {
                    sql.push_str(" WHERE ");
                    sql.push_str(&analysis.conditions.join(" AND "));
                }

                // Add ORDER BY if specified
                if let Some((col, dir)) = &analysis.order_by {
                    sql.push_str(&format!(" ORDER BY {} {}", col, dir));
                }

                // Add LIMIT if specified
                if let Some(limit) = analysis.limit {
                    sql.push_str(&format!(" LIMIT {}", limit));
                }

                sql.push(';');
                Ok((sql, 0.85, QueryType::Select))
            },
            Intent::Insert => {
                let sql = format!("INSERT INTO {} (name, value) VALUES ('New Item', 100);", table_name);
                Ok((sql, 0.75, QueryType::Insert))
            },
            Intent::Update => {
                let mut sql = format!("UPDATE {} SET ", table_name);

                // Default column and value
                sql.push_str("status = 'updated'");

                // Add WHERE clause if conditions exist
                if !analysis.conditions.is_empty() {
                    sql.push_str(" WHERE ");
                    sql.push_str(&analysis.conditions.join(" AND "));
                } else {
                    sql.push_str(" WHERE id = 1");
                }

                sql.push(';');
                Ok((sql, 0.75, QueryType::Update))
            },
            Intent::Delete => {
                let mut sql = format!("DELETE FROM {}", table_name);

                // Add WHERE clause if conditions exist
                if !analysis.conditions.is_empty() {
                    sql.push_str(" WHERE ");
                    sql.push_str(&analysis.conditions.join(" AND "));
                } else {
                    sql.push_str(" WHERE id = 1");
                }

                sql.push(';');
                Ok((sql, 0.75, QueryType::Delete))
            },
            Intent::Count => {
                let sql = format!("SELECT COUNT(*) FROM {};", table_name);
                Ok((sql, 0.9, QueryType::Select))
            },
            Intent::Unknown => {
                let sql = format!("SELECT * FROM {} LIMIT 10;", table_name);
                Ok((sql, 0.5, QueryType::Select))
            }
        }
    }

    /// Generate explanation for the SQL query
    fn generate_explanation(&self, prompt: &str, sql: &str, query_type: &QueryType) -> String {
        match query_type {
            QueryType::Select => {
                format!("This SQL query retrieves data from the database based on your request: '{}'. \
                        It selects records from the table mentioned in your prompt.", prompt)
            },
            QueryType::Insert => {
                format!("This SQL query adds new data to the database based on your request: '{}'. \
                        It inserts a new record into the table mentioned in your prompt.", prompt)
            },
            QueryType::Update => {
                format!("This SQL query modifies existing data in the database based on your request: '{}'. \
                        It updates records in the table mentioned in your prompt.", prompt)
            },
            QueryType::Delete => {
                format!("This SQL query removes data from the database based on your request: '{}'. \
                        It deletes records from the table mentioned in your prompt.", prompt)
            },
            _ => {
                format!("Generated SQL based on your request: '{}'. The query is: {}", prompt, sql)
            }
        }
    }
}

/// Analysis of a natural language prompt
#[derive(Debug)]
struct PromptAnalysis {
    intent: Intent,
    entities: HashMap<String, String>,
    conditions: Vec<String>,
    aggregations: Vec<(String, String)>, // (function, column)
    limit: Option<i32>,
    order_by: Option<(String, String)>, // (column, direction)
}

/// Intent of the SQL query
#[derive(Debug)]
enum Intent {
    Select,
    Insert,
    Update,
    Delete,
    Count,
    Unknown,
}

impl Default for SqlGenerator {
    fn default() -> Self {
        Self::new()
    }
}
